import pandas 
import datetime
from  conf_util import Config
#read conf file 
conf=Config(config_path='myconf.cnf')
user_table = conf.getsection(section='mypath',key='read_path')
outpath=conf.getsection(section='mypath',key='out_ddl_path')
# 定义常量

colString = ''' %s %s,\n'''
drop_table_parttern='''drop table if exists %s.%s ;'''
dws_ddl_partten='''
create table %s.%s ( 
%s
)
WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE)
DISTRIBUTE BY HASH (%s)
PARTITION BY RANGE (partitiondate)(
   PARTITION p_20220531 VALUES LESS THAN ('2022-06-01') TABLESPACE pg_default,
	 PARTITION p_20220601 VALUES LESS THAN ('2022-06-02') TABLESPACE pg_default,
	 PARTITION p_20220602 VALUES LESS THAN ('2022-06-03') TABLESPACE pg_default,
	 PARTITION p_20220603 VALUES LESS THAN ('2022-06-04') TABLESPACE pg_default,
	 PARTITION p_20220604 VALUES LESS THAN ('2022-06-05') TABLESPACE pg_default,
	 PARTITION p_20220605 VALUES LESS THAN ('2022-06-06') TABLESPACE pg_default,
	 PARTITION p_20220606 VALUES LESS THAN ('2022-06-07') TABLESPACE pg_default,
	 PARTITION p_20220607 VALUES LESS THAN ('2022-06-08') TABLESPACE pg_default,
	 PARTITION p_20220608 VALUES LESS THAN ('2022-06-09') TABLESPACE pg_default,
	 PARTITION p_20220609 VALUES LESS THAN ('2022-06-10') TABLESPACE pg_default,
	 PARTITION p_20220610 VALUES LESS THAN ('2022-06-11') TABLESPACE pg_default,
	 PARTITION p_20220611 VALUES LESS THAN ('2022-06-12') TABLESPACE pg_default,
	 PARTITION p_20220612 VALUES LESS THAN ('2022-06-13') TABLESPACE pg_default,
	 PARTITION p_20220613 VALUES LESS THAN ('2022-06-14') TABLESPACE pg_default,
	 PARTITION p_20220614 VALUES LESS THAN ('2022-06-15') TABLESPACE pg_default,
	 PARTITION p_20220615 VALUES LESS THAN ('2022-06-16') TABLESPACE pg_default,
	 PARTITION p_20220616 VALUES LESS THAN ('2022-06-17') TABLESPACE pg_default,
	 PARTITION p_20220617 VALUES LESS THAN ('2022-06-18') TABLESPACE pg_default,
	 PARTITION p_20220618 VALUES LESS THAN ('2022-06-19') TABLESPACE pg_default,
	 PARTITION p_20220619 VALUES LESS THAN ('2022-06-20') TABLESPACE pg_default,
	 PARTITION p_20220620 VALUES LESS THAN ('2022-06-21') TABLESPACE pg_default,
	 PARTITION p_20220621 VALUES LESS THAN ('2022-06-22') TABLESPACE pg_default,
	 PARTITION p_20220622 VALUES LESS THAN ('2022-06-23') TABLESPACE pg_default,
	 PARTITION p_20220623 VALUES LESS THAN ('2022-06-24') TABLESPACE pg_default,
	 PARTITION p_20220624 VALUES LESS THAN ('2022-06-25') TABLESPACE pg_default,
	 PARTITION p_20220625 VALUES LESS THAN ('2022-06-26') TABLESPACE pg_default,
	 PARTITION p_20220626 VALUES LESS THAN ('2022-06-27') TABLESPACE pg_default,
	 PARTITION p_20220627 VALUES LESS THAN ('2022-06-28') TABLESPACE pg_default,
	 PARTITION p_20220628 VALUES LESS THAN ('2022-06-29') TABLESPACE pg_default,
	 PARTITION p_20220629 VALUES LESS THAN ('2022-06-30') TABLESPACE pg_default,
	 PARTITION p_20220630 VALUES LESS THAN ('2022-07-01') TABLESPACE pg_default,
	 PARTITION p_20220701 VALUES LESS THAN ('2022-07-02') TABLESPACE pg_default,
	 PARTITION p_20220702 VALUES LESS THAN ('2022-07-03') TABLESPACE pg_default,
	 PARTITION p_20220703 VALUES LESS THAN ('2022-07-04') TABLESPACE pg_default,
	 PARTITION p_20220704 VALUES LESS THAN ('2022-07-05') TABLESPACE pg_default,
	 PARTITION p_20220705 VALUES LESS THAN ('2022-07-06') TABLESPACE pg_default,
	 PARTITION p_20220706 VALUES LESS THAN ('2022-07-07') TABLESPACE pg_default,
	 PARTITION p_20220707 VALUES LESS THAN ('2022-07-08') TABLESPACE pg_default,
	 PARTITION p_20220708 VALUES LESS THAN ('2022-07-09') TABLESPACE pg_default,
	 PARTITION p_20220709 VALUES LESS THAN ('2022-07-10') TABLESPACE pg_default,
	 PARTITION p_20220710 VALUES LESS THAN ('2022-07-11') TABLESPACE pg_default,
	 PARTITION p_20220711 VALUES LESS THAN ('2022-07-12') TABLESPACE pg_default,
	 PARTITION p_20220712 VALUES LESS THAN ('2022-07-13') TABLESPACE pg_default,
	 PARTITION p_20220713 VALUES LESS THAN ('2022-07-14') TABLESPACE pg_default,
	 PARTITION p_20220714 VALUES LESS THAN ('2022-07-15') TABLESPACE pg_default,
	 PARTITION p_20220715 VALUES LESS THAN ('2022-07-16') TABLESPACE pg_default,
	 PARTITION p_20220716 VALUES LESS THAN ('2022-07-17') TABLESPACE pg_default,
	 PARTITION p_20220717 VALUES LESS THAN ('2022-07-18') TABLESPACE pg_default,
	 PARTITION p_20220718 VALUES LESS THAN ('2022-07-19') TABLESPACE pg_default,
	 PARTITION p_20220719 VALUES LESS THAN ('2022-07-20') TABLESPACE pg_default,
	 PARTITION p_20220720 VALUES LESS THAN ('2022-07-21') TABLESPACE pg_default,
	 PARTITION p_20220721 VALUES LESS THAN ('2022-07-22') TABLESPACE pg_default,
	 PARTITION p_20220722 VALUES LESS THAN ('2022-07-23') TABLESPACE pg_default,
	 PARTITION p_20220723 VALUES LESS THAN ('2022-07-24') TABLESPACE pg_default,
	 PARTITION p_20220724 VALUES LESS THAN ('2022-07-25') TABLESPACE pg_default,
	 PARTITION p_20220725 VALUES LESS THAN ('2022-07-26') TABLESPACE pg_default,
	 PARTITION p_20220726 VALUES LESS THAN ('2022-07-27') TABLESPACE pg_default,
	 PARTITION p_20220727 VALUES LESS THAN ('2022-07-28') TABLESPACE pg_default,
	 PARTITION p_20220728 VALUES LESS THAN ('2022-07-29') TABLESPACE pg_default,
	 PARTITION p_20220729 VALUES LESS THAN ('2022-07-30') TABLESPACE pg_default,
	 PARTITION p_20220730 VALUES LESS THAN ('2022-07-31') TABLESPACE pg_default,
	 PARTITION p_20220731 VALUES LESS THAN ('2022-08-01') TABLESPACE pg_default,
	 PARTITION p_20220801 VALUES LESS THAN ('2022-08-02') TABLESPACE pg_default,
	 PARTITION p_20220802 VALUES LESS THAN ('2022-08-03') TABLESPACE pg_default,
	 PARTITION p_20220803 VALUES LESS THAN ('2022-08-04') TABLESPACE pg_default,
	 PARTITION p_20220804 VALUES LESS THAN ('2022-08-05') TABLESPACE pg_default,
	 PARTITION p_20220805 VALUES LESS THAN ('2022-08-06') TABLESPACE pg_default,
	 PARTITION p_20220806 VALUES LESS THAN ('2022-08-07') TABLESPACE pg_default,
	 PARTITION p_20220807 VALUES LESS THAN ('2022-08-08') TABLESPACE pg_default,
	 PARTITION p_20220808 VALUES LESS THAN ('2022-08-09') TABLESPACE pg_default,
	 PARTITION p_20220809 VALUES LESS THAN ('2022-08-10') TABLESPACE pg_default,
	 PARTITION p_20220810 VALUES LESS THAN ('2022-08-11') TABLESPACE pg_default,
	 PARTITION p_20220811 VALUES LESS THAN ('2022-08-12') TABLESPACE pg_default,
	 PARTITION p_20220812 VALUES LESS THAN ('2022-08-13') TABLESPACE pg_default,
	 PARTITION p_20220813 VALUES LESS THAN ('2022-08-14') TABLESPACE pg_default,
	 PARTITION p_20220814 VALUES LESS THAN ('2022-08-15') TABLESPACE pg_default,
	 PARTITION p_20220815 VALUES LESS THAN ('2022-08-16') TABLESPACE pg_default,
	 PARTITION p_20220816 VALUES LESS THAN ('2022-08-17') TABLESPACE pg_default,
	 PARTITION p_20220817 VALUES LESS THAN ('2022-08-18') TABLESPACE pg_default,
	 PARTITION p_20220818 VALUES LESS THAN ('2022-08-19') TABLESPACE pg_default,
	 PARTITION p_20220819 VALUES LESS THAN ('2022-08-20') TABLESPACE pg_default,
	 PARTITION p_20220820 VALUES LESS THAN ('2022-08-21') TABLESPACE pg_default,
	 PARTITION p_20220821 VALUES LESS THAN ('2022-08-22') TABLESPACE pg_default,
	 PARTITION p_20220822 VALUES LESS THAN ('2022-08-23') TABLESPACE pg_default,
	 PARTITION p_20220823 VALUES LESS THAN ('2022-08-24') TABLESPACE pg_default,
	 PARTITION p_20220824 VALUES LESS THAN ('2022-08-25') TABLESPACE pg_default,
	 PARTITION p_20220825 VALUES LESS THAN ('2022-08-26') TABLESPACE pg_default,
	 PARTITION p_20220826 VALUES LESS THAN ('2022-08-27') TABLESPACE pg_default,
	 PARTITION p_20220827 VALUES LESS THAN ('2022-08-28') TABLESPACE pg_default,
	 PARTITION p_20220828 VALUES LESS THAN ('2022-08-29') TABLESPACE pg_default,
	 PARTITION p_20220829 VALUES LESS THAN ('2022-08-30') TABLESPACE pg_default,
	 PARTITION p_20220830 VALUES LESS THAN ('2022-08-31') TABLESPACE pg_default,
	 PARTITION p_20220831 VALUES LESS THAN ('2022-09-01') TABLESPACE pg_default,
	 PARTITION p_20220901 VALUES LESS THAN ('2022-09-02') TABLESPACE pg_default,
	 PARTITION p_20220902 VALUES LESS THAN ('2022-09-03') TABLESPACE pg_default,
	 PARTITION p_20220903 VALUES LESS THAN ('2022-09-04') TABLESPACE pg_default,
	 PARTITION p_20220904 VALUES LESS THAN ('2022-09-05') TABLESPACE pg_default,
	 PARTITION p_20220905 VALUES LESS THAN ('2022-09-06') TABLESPACE pg_default,
	 PARTITION p_20220906 VALUES LESS THAN ('2022-09-07') TABLESPACE pg_default,
	 PARTITION p_20220907 VALUES LESS THAN ('2022-09-08') TABLESPACE pg_default,
	 PARTITION p_20220908 VALUES LESS THAN ('2022-09-09') TABLESPACE pg_default,
	 PARTITION p_20220909 VALUES LESS THAN ('2022-09-10') TABLESPACE pg_default,
	 PARTITION p_20220910 VALUES LESS THAN ('2022-09-11') TABLESPACE pg_default,
	 PARTITION p_20220911 VALUES LESS THAN ('2022-09-12') TABLESPACE pg_default,
	 PARTITION p_20220912 VALUES LESS THAN ('2022-09-13') TABLESPACE pg_default,
	 PARTITION p_20220913 VALUES LESS THAN ('2022-09-14') TABLESPACE pg_default,
	 PARTITION p_20220914 VALUES LESS THAN ('2022-09-15') TABLESPACE pg_default,
	 PARTITION p_20220915 VALUES LESS THAN ('2022-09-16') TABLESPACE pg_default,
	 PARTITION p_20220916 VALUES LESS THAN ('2022-09-17') TABLESPACE pg_default,
	 PARTITION p_20220917 VALUES LESS THAN ('2022-09-18') TABLESPACE pg_default,
	 PARTITION p_20220918 VALUES LESS THAN ('2022-09-19') TABLESPACE pg_default,
	 PARTITION p_20220919 VALUES LESS THAN ('2022-09-20') TABLESPACE pg_default,
	 PARTITION p_20220920 VALUES LESS THAN ('2022-09-21') TABLESPACE pg_default,
	 PARTITION p_20220921 VALUES LESS THAN ('2022-09-22') TABLESPACE pg_default,
	 PARTITION p_20220922 VALUES LESS THAN ('2022-09-23') TABLESPACE pg_default,
	 PARTITION p_20220923 VALUES LESS THAN ('2022-09-24') TABLESPACE pg_default,
	 PARTITION p_20220924 VALUES LESS THAN ('2022-09-25') TABLESPACE pg_default,
	 PARTITION p_20220925 VALUES LESS THAN ('2022-09-26') TABLESPACE pg_default,
	 PARTITION p_20220926 VALUES LESS THAN ('2022-09-27') TABLESPACE pg_default,
	 PARTITION p_20220927 VALUES LESS THAN ('2022-09-28') TABLESPACE pg_default,
	 PARTITION p_20220928 VALUES LESS THAN ('2022-09-29') TABLESPACE pg_default,
	 PARTITION p_20220929 VALUES LESS THAN ('2022-09-30') TABLESPACE pg_default
);
'''
tab_comment_parttern='''comment on table %s.%s is '%s';
'''
col_comment_parttern='''comment on column  %s.%s.%s is '%s';
'''
#insert 插入模板
insert_col_parttern=''',%s  --%s 
'''
insert_parttern='''
/*
作者: 田川
日期: %s
表名: %s
*/
alter table  %s.%s truncate partition for ('${partitiondate}');
insert  into %s.%s (
%s
) select 
 %s
 from fdc_ods.
;
'''
#read execle file
data_level='fdc_app_xwy'
df = pandas.DataFrame(
    pandas.read_excel(io=user_table, sheet_name='app', usecols=['逻辑表名', '物理表名', '逻辑字段名', '物理字段名', '字段类型'],
                      keep_default_na=False))
# 设置输出文件
file_out = open(outpath+'model_ddl.sql', mode='w', encoding='utf-8')

# 生成dws分区表语句
def gen_dws_ddl(d, sqlparttern, dropparttern,colparttern):
    for key, value in d.items():
        first_name = str() #第一个列英文名
        c_name = str()  # 列格式
        z_name = key.split('==')[0] #表中文名
        e_name = key.split('==')[1] #表英文名
        for item in value.items():
            first_name = item[1].split('+')[0]
            break
        for i in value.items():
            col_name = i[1].split('+')[0] #字段名
            col_type = i[1].split('+')[1] #字段类型  i[0] 是key,字段中文名
            if col_type == '':
                col_type = 'varchar'
            c_name += colparttern % (col_name, col_type)
        index = c_name.rfind(',')
        c_name = c_name[:index+1]
        file_out.write(dropparttern % (data_level,e_name))
        file_out.write(sqlparttern % (data_level,e_name, c_name, first_name))
        file_out.write(tab_comment_parttern%(data_level,e_name,z_name))
        for i in value.items():
            col_name = i[1].split('+')[0] #字段名 i[0] 是key,字段中文名
            file_out.write(col_comment_parttern%(data_level,e_name,col_name,i[0]))
# 生成dws 分区插入语句
def gen_dws_part_insert(d, colPattern, insertPattern):
    for key, value in d.items():
        c_name = str()
        z_name = str()
        z_table_name = key.split('==')[0]#表中文名
        e_table_name = key.split('==')[1]#表英文名
        for k, v in value.items():
            col_name = v.split('+')[0]#字段名
            z_col_name = k  #字段中文名
            c_name += colPattern % (col_name, z_col_name)
            z_name += '''--%s \n''' % z_col_name
        idx = c_name.find(',')
        c_name = c_name[idx + 1:]
        with open(outpath + e_table_name.strip() + '.sql', mode='w', encoding='utf-8') as out:
            out.write(insertPattern % (str(datetime.date.today()), z_table_name,data_level ,e_table_name,data_level,e_table_name, c_name, z_name))
    pass



# {表名：{逻辑字段名：物理字段名+字段类型}}
d = dict()
d_col = None
table_name = str()
for word in df.values:
    if word[0] != '':
        table_name = str.strip(word[0]) + '==' + str.strip(word[1])
        d_col = dict()
        if table_name is not None:
            d[table_name] = d_col
            d_col.clear()
    d_col[word[2]] = word[3] + '+' + word[4]
print(d)
gen_dws_ddl(d,dws_ddl_partten,drop_table_parttern,colString)
gen_dws_part_insert(d,insert_col_parttern,insert_parttern)
file_out.close()
